/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Iterator;

/**
 *
 * @author ben
 */
public class biStockTobaccoJdbcDao extends JdbcDaoSupport implements biStockTobaccoDataDao  {

    @Override
    public void biStockTobaccoData(Date adTsnDate, int aiShopId, String asShopBm, int aiDateNo, String asYear, int aiYybh, int aiMan_id, String asTname) {
        int iSaleAmt = 0, iEncashAmt = 0;
        int iRc = 0;
        int aiYear = 0;
        String sql = "";
        float fTotalValue = 0, fTobValue = 0;
        List lstTobacco;
        //将数据库设置为固定的t_spsjk该表运行于168.42.8.10机器上，为分区表
        SimpleDateFormat ddd = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Calendar rightNow = Calendar.getInstance();
        rightNow.add(Calendar.DATE, -1);
        String lsYesterday = ddd.format(rightNow.getTime());
        java.sql.Date ldYesterday = java.sql.Date.valueOf(lsYesterday);

        if (adTsnDate.before(ldYesterday)) {
            //汇总表
            asTname = "dvlpr_asm.t_spsjk_old ";
        } else {
            //工作表，只存储昨天和前天的数据
            asTname = "dvlpr_asm.t_spsjk ";
            //sTname = " t_waste_book ";
        }

        System.out.println("now processing  "+asTname+"  biStockTobacco's Data... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        //取总库存金额
        JdbcTemplate jt = getJdbcTemplate();
        sql = "select round(nvl(sum(jj * sl),0),2) as total_value from " + asTname +
                " where spkdate = ? and bm=?  ";
        Object[] params = new Object[]{adTsnDate, asShopBm};
        int[] types = {Types.DATE, Types.VARCHAR};
        lstTobacco = jt.query(sql, params, types, new RowMapperResultReader(new TobaccoMxRM()));
        Iterator Iter = lstTobacco.iterator();
        biStockTobacco ltam = new biStockTobacco();
        while (Iter.hasNext()) {
            ltam = (biStockTobacco) Iter.next();
            fTotalValue = ltam.getTotal_value();
        }
        //取烟草类商品的库存金额
        sql = "select round(nvl(sum(jj * sl),0),2) as tob_value from " + asTname +
                " where spkdate = ? and bm=?  and substr(lb,1,3)='008' ";
        lstTobacco = jt.query(sql, params, types, new RowMapperResultReader(new TobaccoMxRM2()));
        Iter = lstTobacco.iterator();
        while (Iter.hasNext()) {
            ltam = (biStockTobacco) Iter.next();
            fTobValue = ltam.getTob_value();
        }

        //begin insert into lotteryMx
        //1:delete
        params = new Object[]{adTsnDate, aiShopId};
        types = new int[]{Types.DATE, Types.INTEGER};
        sql = "delete from t_bi_stock_tobacco where tsn_date = ? and tsn_id = ?";
        iRc = jt.update(sql, params, types);
        //2:insert
        sql = "insert into t_bi_stock_tobacco " +
                "(tsn_date,tsn_id,yybh,man_id,total_value,tob_value)" +
                " values(?,?,?,?,?,?)";
        params = new Object[]{adTsnDate, aiShopId, aiYybh, aiMan_id,
             fTotalValue,  fTobValue  };
        types = new int[]{Types.DATE, Types.INTEGER,
            Types.INTEGER, Types.INTEGER, Types.FLOAT,  Types.FLOAT  };
        iRc = jt.update(sql, params, types);
    }

    class TobaccoMxRM implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            biStockTobacco lmx = new biStockTobacco();
            lmx.setTotal_value(rs.getFloat("total_value"));
            return lmx;
        }
    }

    class TobaccoMxRM2 implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            biStockTobacco lmx = new biStockTobacco();
            lmx.setTob_value(rs.getFloat("tob_value"));
            return lmx;
        }
        
    }

}
